# 读取Excel文件
import openpyxl
# 导入随机生成学生考试成绩的模块
from day07.Until.studentdemo import StudentInfo

class ExcelOperator:
    # 构造函数
    def __init__(self, path:str, sheet:str, infos, flag:bool=True):
        self.path = path
        self.sheet = sheet
        self.infos = infos # 如果格式需要dict类型，需要提供key,infos就是提供的key
        self.flag = flag # 如果Flag是True,表明有表头，意味着数据从第二行开始，如果Flag是False,没有表头

        # 保存Excel数据的集合
        self.student01 = []  # [[],[],[],....]
        self.student02 = []  # [{},{},{},{},.....]

        # 自动读取
        # self.read_file()

    def read_file(self):
        # 实例化一个
        workbook = openpyxl.load_workbook(self.path)
        # 定义一个sheet
        sheet = workbook[self.sheet]
        # 开始读取
        for index, row in enumerate(sheet.rows): # sheet.rows()-- 返回这个表格有多少行
            # 判断有没有表头
            if self.flag and index == 0:
                continue

            # 定义一个集合存储
            one_row_list = [] # 为了存储[[],[],[],....]
            one_row_dict = {}.fromkeys(self.infos) # 为了存储[{},{},{},{},.....]


            # 开始遍历一行的数据
            for col_index, col_value in enumerate(row):
                # ===把每一个单元格附加到one_row_list中
                one_row_list.append(col_value.value)
                # ===准备字典
                one_row_dict[self.infos[col_index]] = col_value.value

            # 附加到第一种类型的集合中--[[],[],[],....]
            self.student01.append(one_row_list)
            # 附加到第二种类型的集合中--[{},{},{},{},.....]
            self.student02.append(one_row_dict)





class ExcelWrite:
    # 构造函数
    def __init__(self, path: str, sheet: str, infos:list=""):
        self.path = path
        self.sheet = sheet
        self.infos = infos # 如果info为空不写表头，如果不为空，就写表头


    def write_file(self, data):
        """写入到Excel文件"""
        # 实例化一个workbook
        workbook = openpyxl.Workbook()
        # 激活一个sheet
        sheet = workbook.active
        # 为sheet设置一个title
        sheet.title = self.sheet

        # 写入表头
        if len(self.infos) != 0:
            # 把数据第一个位置插入表头
            data.insert(0, self.infos)

        # 开始遍历 --- [[],[],[],[],]
        for index, row in enumerate(data):
            # 遍历每一行
            for col_index, col_val in enumerate(row):
                # 写入
                sheet.cell(row=index + 1, column=col_index + 1, value=col_val)

        # 写入真实的Excel
        workbook.save(self.path)
        print("写入成功！")


if __name__ == '__main__':
    """
    ======================读取===============
       # 准备一个path
    path =  r"E:\Python\Project\BasciProject\student.xlsx"
    # 准备sheet
    sheet = "student"
    infos = ['sno','name','gender','birthday','mobile','email','address']
    # 实例化对象
    obj01 = ExcelOperator(path, sheet, infos, True)
    # 打印
    print(obj01.student01)
    print(obj01.student02)
    """
    # 写入
    path = r"E:\Python\Project\BasciProject\Exam06.xlsx"
    sheet = "初三(3)班第一学期考试成绩"
    info = ['学号','姓名','性别','班级','语文','数学','英语','物理','化学']
    # 准备数据
    obj01 = StudentInfo()

    # 生成数据
    obj01.get_student_result_four()
    # 实例化
    obj_write = ExcelWrite(path, sheet, info)
    # 写入
    obj_write.write_file(obj01.results04)